﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using PublicTransport.Services;

namespace PublicTransport.DAL
{
    /// <summary>  Rozhranie s DB, ktoré sprístupňuje objekty Reacheable_table  </summary>
    public class TimeTableDao
    {

        /// <summary> Zdieľané pripojenie, ktoré sa nezatvára počas existencie objektu </summary>
        SQLiteConnection connection;

        /// <summary>   Vytvorí sa spojenie s DB </summary>
        public TimeTableDao()
        {
            this.connection = DBHelper.GetConnection();
        }

        /// <summary>   Vráti odchody zo stanice podľa kritérií </summary>
        ///
        /// <param name="stationId">    ID štartovnej stanice </param>
        /// <param name="startTime">    Čas v ktorom hľadáme spoje </param>
        /// <param name="dayType">      Typ ďna - pracovný, voľný, víkend</param>
        /// <param name="waitTime">     Ako dlho sme ochotný čakať v minútach</param>
        /// <param name="linesToUse">   Ktoré linky chceme použiť </param>
        ///
        /// <returns>   Spoj, ktorý použijeme. Najlepšia varianta</returns>

        public Line GetLineTime(int stationId, int startTime, String dayType, int waitTime, List<int> linesToUse)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < linesToUse.Count; i++)
            {
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append((linesToUse[i]).ToString());

            }

            SQLiteCommand cmnd = new SQLiteCommand(connection);
            cmnd.CommandText = "SELECT line_id, min(time) AS time FROM time_table WHERE station_id=@stationId AND line_id IN " +
                                "(" + sb.ToString() + ") and time>@startTime and time<@endTime and day_type=@dayType GROUP BY line_id ORDER BY time ASC LIMIT 1";
            cmnd.Parameters.AddWithValue("stationId", stationId);
            cmnd.Parameters.AddWithValue("startTime", startTime.ToString());
            cmnd.Parameters.AddWithValue("endTime", (startTime + waitTime).ToString());
            cmnd.Parameters.AddWithValue("dayType", dayType);

            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Line line = new Line();
                        line.LineID = int.Parse(reader["line_id"].ToString());
                        line.Time = int.Parse(reader["time"].ToString());
                        return line;
                    }
                    return null;
                }
            }

        }

        /// <summary>   Gets a line times. </summary>
        ///
        /// <remarks>   Daniel Novak, 25. 4. 2011. </remarks>
        ///
        /// <param name="stationId">    ID štartovnej stanice. </param>
        /// <param name="startTime">    Čas v ktorom hľadáme spoje. </param>
        /// <param name="dayType">      Typ ďna - pracovný, voľný, víkend. </param>
        /// <param name="waitTime">     Ako dlho sme ochotný čakať v minútach. </param>
        /// <param name="linesToUse">   Ktoré linky chceme použiť. </param>
        ///
        /// <returns>   Rôzne linky, ktoré vyhovujú kritériám </returns>

        public List<Line> GetLineTimes(int stationId, int startTime, String dayType, int waitTime, List<int> linesToUse)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < linesToUse.Count; i++)
            {
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append((linesToUse[i]).ToString());

            }

            SQLiteCommand cmnd = new SQLiteCommand(connection);
            cmnd.CommandText = "SELECT line_id, time AS time FROM time_table WHERE station_id=@stationId AND line_id IN " +
                                "(" + sb.ToString() + ") and time>@startTime and time<@endTime and day_type=@dayType ORDER BY time ";
            cmnd.Parameters.AddWithValue("stationId", stationId);
            cmnd.Parameters.AddWithValue("startTime", startTime.ToString());
            cmnd.Parameters.AddWithValue("endTime", (startTime + waitTime).ToString());
            cmnd.Parameters.AddWithValue("dayType", dayType);

            List<Line> lines = new List<Line>();
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Line line = new Line();
                        line.LineID = int.Parse(reader["line_id"].ToString());
                        line.Time = int.Parse(reader["time"].ToString());
                        lines.Add(line);
                    }
                    return lines;
                }
            }

        }
    }
}
